package com.content.rongbay;

import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import com.db.MysqlConnect;
import com.util.VietDecode;

import net.htmlparser.jericho.*;

public class City {
	public static void main(String[] args) {
		City ct = new City();
		ct.getCity("file:///C:/Users/Public/Documents/item.php.htm");
	}
	
	/*
	 * get info tinh thanh
	 */
	public void getCity(String url){
		try {
			//System.out.println("get City");
			Source source=new Source(new URL(url));
			source.fullSequentialParse();
			//List<Element> tbl = source.getAllElements("class", "City", false);	
			List<Element> tbl=source.getAllElements(HTMLElementName.A);
			
			int size = tbl.size();
			String name=null;
			Element e=null;
			VietDecode vd = new VietDecode();
			String code=null;
			//System.out.println(size);
			
			for (int it=0;it<size;it++) {
				if (it<tbl.size()){
					e = (Element)tbl.get(it);					
					name = CharacterReference.decodeCollapseWhiteSpace(e.getContent());
					name = name.replaceAll("<b>","").replaceAll("</b>","").trim();
					code = vd.convert(name);
					code = code.replaceAll(" ","").trim().toLowerCase();
					System.out.println(name);
					System.out.println(code);
					System.out.println(insert(code, name));
				}
			}
		} catch (Exception e) {
			System.out.println("---"+e.toString());
		}
		
	}
	
	public String insert(String p_code,String p_name){
		try {
			String result = "E001|Co loi phat sinh";
			PreparedStatement pstmt = null;
		    Connection conn  = null;
		    MysqlConnect mysql = new MysqlConnect();	
		    try {
		    	conn = mysql.getConnection();		    	
			    conn.setAutoCommit(false);
			    
			    String vc_sql="SELECT id FROM  `city` WHERE `code`=?";
			    int id=0;
			    pstmt = conn.prepareStatement(vc_sql);
			    pstmt.setString(1, p_code);
			    ResultSet rs = pstmt.executeQuery();
				while (rs.next())
					id= rs.getInt("id");				
				//san pham da duoc cap nhat
				if(id > 0) return "E002|Da ton tai tinh!";
				
		    	vc_sql ="INSERT INTO `city` (`id`,`name`,`code`) VALUES (NULL,?,?)";				
				pstmt = conn.prepareStatement(vc_sql);
				
				pstmt.setString(1,p_name);
				pstmt.setString(2,p_code);				
				
				int ext = pstmt.executeUpdate();
				if(ext>0){
					result="S001";
					conn.commit();
				}
				pstmt.close();
			    conn.close();
			    
		    }catch (Exception e) {
				System.err.println("Error: " + e.getMessage());
			    e.printStackTrace();
			    return result;
		    } finally {
		    	pstmt.close();
		    	conn.close();
		    }
			return result;
		} catch (Exception e) {
			return "E001|Co loi phat sinh";
		}
	}
}
